﻿Imports System.Data
Imports System.Data.SqlClient
Public Class CentroCustoDAL
#Region "Métodos"
#Region "Incluir" '(ByVal subcod As Integer, ByVal grupoCod As Integer, subNome As String)
    Public Function Inserir(ByRef grupcod As Integer, ByVal Subnome As String) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "insert into Financeiro.Gen_SubGrupo(gru_codigo, sgr_nome) values (@grupcod,@subnome)"
            cmd.Parameters.Add(New SqlParameter("@grupcod", SqlDbType.Int)).Value = grupcod
            cmd.Parameters.Add(New SqlParameter("@subnome", SqlDbType.VarChar)).Value = Subnome
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region
#Region "Alterar" '
    Public Function Alterar(ByVal grucod As Integer, ByVal subnome As String, ByVal sgrcodigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "update Financeiro.Gen_SubGrupo set gru_codigo=@grucodigo, sgr_nome=@subnome where sgr_codigo=@sgrcodigo"
            cmd.Parameters.Add(New SqlParameter("@grucodigo", SqlDbType.Int)).Value = grucod
            cmd.Parameters.Add(New SqlParameter("@subnome", SqlDbType.VarChar)).Value = subnome
            cmd.Parameters.Add(New SqlParameter("@sgrcodigo", SqlDbType.Int)).Value = sgrcodigo
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region
#End Region
#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, filtro As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case filtro
                Case 0
                    cmd.CommandText = "select * from vw_FinCentroCusto where sgr_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_FinCentroCusto where sgr_nome COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case Else
                    cmd.CommandText = "select * from vw_FinCentroCusto where gru_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"

            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "valida"
    Function valida(ByVal codigo As Integer, ByVal nome As String, ByVal tipo As Integer) As Boolean
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM Financeiro.Gen_SubGrupo where sgr_nome=@nome and gru_codigo=@tipo and sgr_codigo <> @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = nome
            cmd.Parameters.Add(New SqlParameter("@tipo", SqlDbType.Int)).Value = tipo
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False  'nao encontrou
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
#Region "localiza"
    Function localiza() As DataTable
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FinCentroCusto order by gru_descricao"
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "busca"
    Function buscaralt(ByVal codigo As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Financeiro.Gen_SubGrupo where sgr_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
End Class
